package me.zhengjie.gas.repositories;

import me.zhengjie.gas.dto.TransactionDataDto;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.UUID;


@Repository
public interface TransactionRepository extends JpaRepository<TransactionDataDto, Integer> {

    @Query(value = "select * from {h-schema}transaction_data where trans_uuid=:uuidStr", nativeQuery = true)
    TransactionDataDto findByUuidStr(@Param("uuidStr") UUID uuidStr);

    @Query(value = "select * from {h-schema}transaction_data " +
            "where dept_id in (:deptIds) and transaction_time >= :startDate and transaction_time <= :endDate " +
            "order by transaction_time desc", nativeQuery = true)
    List<TransactionDataDto> findAllByDeptIds(@Param("startDate")LocalDateTime startDate, @Param("endDate")LocalDateTime endDate, @Param("deptIds")List<Long> deptIds);

    @Query(value = "select * from {h-schema}transaction_data " +
            "where dept_id = :deptId and (:lineNo IS NULL or line_id=:lineNo) and transaction_time >= :startDate and transaction_time <= :endDate " +
            "order by transaction_time desc", nativeQuery = true)
    List<TransactionDataDto> findAllByDeptId(@Param("startDate")LocalDateTime startDate, @Param("endDate")LocalDateTime endDate,
                                             @Param("deptId")Long deptId, @Param("lineNo")Integer lineNo);

    @Query(value = "select * from {h-schema}transaction_data " +
            "where dept_id = :deptId and line_id = :lineId and (:deviceId is null or device_id=:deviceId) " +
            "order by transaction_time desc limit 1", nativeQuery = true)
    TransactionDataDto findLastByDeptIdAndLineId(@Param("deptId")Long deptId, @Param("lineId")Integer lineId, @Param("deviceId")String deviceId);

    @Query(value = "select * from {h-schema}transaction_data " +
            "where dept_id = :deptId order by transaction_time desc limit :pageSize offset :offset", nativeQuery = true)
    List<TransactionDataDto> findLastByDeptId(Long deptId, Integer pageSize, Integer offset);

    @Query(value = "select * from {h-schema}transaction_data " +
            "where dept_id in (:deptIds) and logic_card_id = :logicCardNo and physical_card_id=:physicalCardNo order by transaction_time", nativeQuery = true)
    List<TransactionDataDto> findLastByCardNo(@Param("deptIds") List<Long> deptIds, @Param("logicCardNo")String logicCardNo, @Param("physicalCardNo")String physicalCardNo);
}
